﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using VonaJobs.DAL.DAO;

namespace VonaJobs.DAL.Gatway
{
    public class JobGateway:DBGateway
    {
        public string SaveNewJob(Job job)
        {

            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText =
                    string.Format("insert into tblJob values(@PostingDate,@ClosingDate,@ApplyMode,@JobTitle,@LocationId,@JobCategoryId,@JobType,@WorkType,@Salary,@NumberOfVacancy,@JobDescription,@RequiredSkill,@Experience,@Active,@Status)");
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@PostingDate", SqlDbType.DateTime).Value = job.PostingDate;
                CommandObj.Parameters.Add("@ClosingDate", SqlDbType.DateTime).Value = job.ClosingDate;
                //CommandObj.Parameters.Add("@ApplyMode", SqlDbType.NVarChar).Value = job.ApplyMode;
                CommandObj.Parameters.Add("@JobTitle", SqlDbType.NVarChar).Value = job.JobTitle;
                CommandObj.Parameters.Add("@LocationId", SqlDbType.Int).Value = job.Location.Id;
                CommandObj.Parameters.Add("@JobCategoryId", SqlDbType.Int).Value = job.JobCategory.Id;
                CommandObj.Parameters.Add("@JobType", SqlDbType.NVarChar).Value = job.JobType;
                CommandObj.Parameters.Add("@WorkType", SqlDbType.NVarChar).Value = job.WorkType;
                CommandObj.Parameters.Add("@Salary", SqlDbType.Int).Value = job.Salary;
                CommandObj.Parameters.Add("@NumberOfVacancy", SqlDbType.Int).Value = job.NumberOfVacancy;
                CommandObj.Parameters.Add("@JobDescription", SqlDbType.NVarChar).Value = job.JobDescription;
                CommandObj.Parameters.Add("@RequiredSkill", SqlDbType.NVarChar).Value = job.RequiredSkill;
                CommandObj.Parameters.Add("@Experience", SqlDbType.Int).Value = job.Experience;
                CommandObj.Parameters.Add("@Active", SqlDbType.Bit).Value = job.Active;
                CommandObj.Parameters.Add("@Status", SqlDbType.NVarChar).Value = job.Status;
                CommandObj.ExecuteNonQuery();
                return "New job created successfully";
            }
            catch (Exception)
            {
                throw new Exception("Message Not Send, Server Error !");
            }
            finally
            {
                if (ConnectionObj != null && ConnectionObj.State == ConnectionState.Open)
                {
                    ConnectionObj.Close();
                }

            }
        }



        public List<Job>GetTotalJobList()
        {
            try
            {
                List<Job> joblists=new List<Job>();
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format("select * from tblJobs order by JobTitle");
                SqlDataReader reader = CommandObj.ExecuteReader();
                while (reader.Read())
                {
                    Job aJob=new Job();
                    aJob.Id = Convert.ToInt32(reader[0]);
                    aJob.JobTitle = reader[1].ToString();
                    aJob.PostingDate = Convert.ToDateTime(reader[2]);
                    aJob.ClosingDate = Convert.ToDateTime(reader[3]);
                    aJob.ViewThisJob = Convert.ToInt32(reader[4]);

                }
                return joblists;

            }
            catch (Exception)
            {

                throw new Exception("Error occured while Reading Job, Please Try again !");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public List<Job> GetAllPostedJob()
        {
            List<Job> jobs = new List<Job>();
            try
            {
                ConnectionObj.Open();
                string select = String.Format("Select * from tblJob Where status='{0}'", 0);
                CommandObj.CommandText = select;
                SqlDataReader reader = CommandObj.ExecuteReader();
                while (reader.Read())
                {
                    Job aJob = new Job();
                    aJob.PostingDate = Convert.ToDateTime(reader[0]);
                    aJob.ClosingDate = Convert.ToDateTime(reader[1]);
                    aJob.JobTitle = Convert.ToString(reader[2]);
                    aJob.Location.Id = Convert.ToInt32(reader[4]);
                    aJob.JobCategory.Id = Convert.ToInt32(reader[5]);
                    aJob.JobType = Convert.ToString(reader[5]);
                    aJob.WorkType = Convert.ToString(reader[6]);
                    aJob.JobDescription = Convert.ToString(reader[7]);
                    aJob.Salary = Convert.ToInt32(reader[8]);
                    aJob.NumberOfVacancy = Convert.ToInt32(reader[9]);
                    aJob.RequiredSkill = Convert.ToString(reader[10]);
                    aJob.Experience = Convert.ToInt32(reader[11]);
                    aJob.Active = Convert.ToBoolean(reader[12]);
                    aJob.Status = Convert.ToString(reader[13]);
                    jobs.Add(aJob);

                }

            }
            catch (Exception)
            {
                throw new Exception("Operation failed Server Error !");
            }
            finally
            {
                if (ConnectionObj != null && ConnectionObj.State == ConnectionState.Open)
                {
                    ConnectionObj.Close();
                }

            }
            return jobs;
        }
    }
}